﻿CREATE TABLE [dbo].[SponsorEnrollment] (

	[Id] [dbo].[Id] NOT NULL  IDENTITY(1000000000, 1), 

	[SponsorId] [dbo].[Id] NOT NULL,

	[ParentSponsorId] [dbo].[Id] NULL,
  
	[ProgramId] [dbo].[Id] NOT NULL,

	[EffectiveDate] [dbo].[EffectiveDate] NOT NULL,

	[TerminationDate] [dbo].[TerminationDate] NOT NULL,

	[Voided] [dbo].[Voided] NOT NULL,

	[EnrollmentActionReasonId] [dbo].[Id] NOT NULL,

	[TerminationActionReasonId] [dbo].[Id] NULL,

	[VoidedActionReasonId] [dbo].[Id] NULL,

    [CreateId] [dbo].[Name] NOT NULL DEFAULT SUSER_NAME () , 

    [CreateDateTime] [dbo].[CreateModifiedDateTime] NOT NULL DEFAULT GETDATE() , 

    [ModifiedId] [dbo].[Name] NOT NULL DEFAULT SUSER_NAME (),

    [ModifiedDateTime] [dbo].[CreateModifiedDateTime] NOT NULL DEFAULT GETDATE(), 
 
    CONSTRAINT [PK_SponsorEnrollment] PRIMARY KEY ([Id]), 

	CONSTRAINT [UNQ_SponsorEnrollment_SponsorIdParentSponsorIdEffectiveDate] UNIQUE (SponsorId, ParentSponsorId, EffectiveDate, Voided),

	CONSTRAINT [UNQ_SponsorEnrollment_SponsorIdParentSponsorIdTerminationDate] UNIQUE (SponsorId, ParentSponsorId, TerminationDate, Voided),

	CONSTRAINT [FK_SponsorEnrollment_SponsorId] FOREIGN KEY (SponsorId) REFERENCES Sponsor (Id),

	CONSTRAINT [FK_SponsorEnrollment_ParentSponsorId] FOREIGN KEY (ParentSponsorId) References Sponsor (Id),

	CONSTRAINT [FK_SponsorEnrollment_ProgramId] FOREIGN KEY (ProgramId) REFERENCES Program (Id)

)



GO

CREATE INDEX [IX_SponsorEnrollment_ParentSponsorEffectiveTermination] ON [dbo].[SponsorEnrollment] (ParentSponsorId, EffectiveDate, TerminationDate, Voided)

GO

CREATE INDEX [IX_SponsorEnrollment_EffectiveTermination] ON [dbo].[SponsorEnrollment] (EffectiveDate, TerminationDate, Voided) INCLUDE (SponsorId)

GO

CREATE INDEX [IX_SponsorEnrollment_TerminationEffective] ON [dbo].[SponsorEnrollment] (TerminationDate, EffectiveDate, Voided) INCLUDE (SponsorId)

GO

